{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.7.4"
    },
    "nbTranslate": {
      "displayLangs": [
        "*"
      ],
      "hotkey": "alt-t",
      "langInMainMenu": true,
      "sourceLang": "en",
      "targetLang": "fr",
      "useGoogleTranslate": true
    },
    "toc": {
      "base_numbering": 1,
      "nav_menu": {},
      "number_sections": true,
      "sideBar": true,
      "skip_h1_title": false,
      "title_cell": "Table of Contents",
      "title_sidebar": "Contents",
      "toc_cell": false,
      "toc_position": {},
      "toc_section_display": true,
      "toc_window_display": false
    },
    "varInspector": {
      "cols": {
        "lenName": 16,
        "lenType": 16,
        "lenVar": 40
      },
      "kernels_config": {
        "python": {
          "delete_cmd_postfix": "",
          "delete_cmd_prefix": "del ",
          "library": "var_list.py",
          "varRefreshCmd": "print(var_dic_list())"
        },
        "r": {
          "delete_cmd_postfix": ") ",
          "delete_cmd_prefix": "rm(",
          "library": "var_list.r",
          "varRefreshCmd": "cat(var_dic_list()) "
        }
      },
      "position": {
        "height": "144px",
        "left": "893px",
        "right": "20px",
        "top": "92px",
        "width": "350px"
      },
      "types_to_exclude": [
        "module",
        "function",
        "builtin_function_or_method",
        "instance",
        "_Feature"
      ],
      "window_display": true
    },
    "colab": {
      "name": "tushare_load.ipynb",
      "provenance": [],
      "include_colab_link": true
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/xinihe/jrxy_portfolio_management/blob/master/Code/tushare_load.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ofBZHfum5xYO"
      },
      "source": [
        "!pip install tushare\n",
        "!pip install sqlalchemy\n",
        "!pip install pymysql"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Q3tbywTcvCGl"
      },
      "source": [
        "\"\"\"\n",
        "Created on 6 April 2021  @author: Dai Yihang\n",
        "\"\"\"\n",
        "import pandas as pd\n",
        "import numpy as np\n",
        "import tushare as ts\n",
        "import random\n",
        "import time\n",
        "from sqlalchemy import create_engine\n",
        "\n",
        "token_poor = ['token']\n",
        "\n",
        "def get_tushare_data(token_poor,function_name,retry_count=3,pause=2,**kwargs):\n",
        "    '''\n",
        "    从tushare导入数据的重试函数\n",
        "    pro:设置完token的tushare内置的pro对象\n",
        "    function_name:pro对象内置的请求数据的函数名，用字符串表示\n",
        "    retry_count：重试次数\n",
        "    pause：请求失败后的暂停时间\n",
        "    **kwargs：给function_name函数传入的参数\n",
        "    '''\n",
        "    for _ in range(retry_count):\n",
        "        try:\n",
        "            pro = ts.pro_api(random.choice(token_poor))\n",
        "            data = getattr(pro,function_name)(**kwargs)\n",
        "        except:\n",
        "            time.sleep(pause)\n",
        "        else:\n",
        "            return data\n",
        "        \n",
        "con = create_engine('mysql+pymysql://username:password@IP/test')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2-Fh8h2gvCGt",
        "outputId": "2e74ba32-3094-4bc9-debc-63789c0a391f"
      },
      "source": [
        "code_df = get_tushare_data(token_poor,'index_weight',index_code='399300.SZ', start_date='20210101', end_date='20210402')\n",
        "code_list = list(set(code_df['con_code'].tolist()))\n",
        "code_list[:5]"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['600436.SH', '601990.SH', '300142.SZ', '600297.SH', '300676.SZ']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 2
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VdW4OdmjvCGv",
        "outputId": "d762f2a5-4765-43d6-cb3b-d059588493c9"
      },
      "source": [
        "# code=code_list[0]\n",
        "import tqdm\n",
        "for code in tqdm.tqdm(code_list):\n",
        "    daily_df = get_tushare_data(token_poor,'daily',ts_code=code, start_date='20190101', end_date='20210101')\n",
        "    daily_df.to_sql(code[:6],con,if_exists='append',index=False)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "100%|████████████████████████████████████████| 300/300 [00:46<00:00,  6.39it/s]\n"
          ],
          "name": "stderr"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AHrxBSOqvCGv"
      },
      "source": [
        "df_300 = get_tushare_data(token_poor,'index_daily',ts_code='399300.SZ', start_date='20190101', end_date='20210101')\n",
        "df_300.to_sql('399300',con,if_exists='append',index=False)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "f7MuY1ZFvCGw"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}